﻿
USE [Pulog]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 06/01/2009 17:00:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
	[categoryID] [int] NOT NULL,
	[categoryName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[categoryDesc] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[categoryOrder] [int] NULL,
	[categoryIcon] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[categoryURL] [text] COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[categoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



USE [Pulog]
GO
/****** Object:  Table [dbo].[User]    Script Date: 06/01/2009 16:59:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[userID] [int] NOT NULL,
	[userName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[regTime] [datetime] NULL,
	[email] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[homepage] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[QQ] [int] NULL,
	[msn] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[info] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[gender] [int] NULL,
	[location] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[regIP] [text] COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[userID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




USE [Pulog]
GO
/****** Object:  Table [dbo].[Post]    Script Date: 06/01/2009 17:00:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Post](
	[postID] [int]  NOT NULL,
	[title] [text] COLLATE Chinese_PRC_CI_AS NOT NULL,
/*	[pubTime] [datetime] NOT NULL, */
	[pubTime] [int] NOT NULL,
	[authorID] [int] NOT NULL,
	[content] [text] COLLATE Chinese_PRC_CI_AS NOT NULL,
	[categoryID] [int] NOT NULL,
	[views] [int] NOT NULL,
	[sticky] [int] NOT NULL,
	[slug] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[property] [int] NOT NULL,
	/*[tt] [int] NULL,*/
	[tt] [datetime] NULL,
 CONSTRAINT [PK_blogs] PRIMARY KEY CLUSTERED 
(
	[postID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[Post]  WITH NOCHECK ADD  CONSTRAINT [FK_blogs_Category] FOREIGN KEY([categoryID])
REFERENCES [dbo].[Category] ([categoryID])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_blogs_Category]
GO
ALTER TABLE [dbo].[Post]  WITH NOCHECK ADD  CONSTRAINT [FK_blogs_User] FOREIGN KEY([authorID])
REFERENCES [dbo].[User] ([userID])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_blogs_User]







USE [Pulog]
GO
/****** Object:  Table [dbo].[Comment]    Script Date: 06/01/2009 17:00:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Comment](
	[CommentID] [int]  NOT NULL,
	[CommentProperty] [int] NULL,
	[PostID] [int] NOT NULL,
	[CommentTime] [datetime] NOT NULL,
	[AuthorID] [int] NULL,
	[AuthorName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[AuthorEmail] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[AuthorURL] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[AuthorIP] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[CommentContent] [text] COLLATE Chinese_PRC_CI_AS NOT NULL,
	[AdminReplyID] [int] NULL,
	[AdminReplyName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[AdminReplyTime] [datetime] NULL,
	[AdminReplyContent] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[CommentPassword] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[IsApproved] [bit] NULL,
 CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED 
(
	[CommentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 - public 
1 - hidden
2 - waiting for pass public
3 - waiting for pass hidden' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment', @level2type=N'COLUMN',@level2name=N'CommentProperty'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'-1 - guest' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Comment', @level2type=N'COLUMN',@level2name=N'AuthorID'
GO
ALTER TABLE [dbo].[Comment]  WITH CHECK ADD  CONSTRAINT [FK_Comment_Post] FOREIGN KEY([PostID])
REFERENCES [dbo].[Post] ([postID])
GO
ALTER TABLE [dbo].[Comment] CHECK CONSTRAINT [FK_Comment_Post]



USE [Pulog]
GO
/****** Object:  Table [dbo].[Tags]    Script Date: 06/01/2009 17:01:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tags](
	[TagID] [int] NOT NULL,
	[TagName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED 
(
	[TagID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



USE [Pulog]
GO
/****** Object:  Table [dbo].[PostTag]    Script Date: 06/01/2009 17:01:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PostTag](
	[PostTagID] [int] NOT NULL,
	[PostID] [int] NOT NULL,
	[TagID] [int] NOT NULL,
 CONSTRAINT [PK_PostTag] PRIMARY KEY CLUSTERED 
(
	[PostTagID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PostTag]  WITH CHECK ADD  CONSTRAINT [FK_PostTag_Post] FOREIGN KEY([PostID])
REFERENCES [dbo].[Post] ([postID])
GO
ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Post]
GO
ALTER TABLE [dbo].[PostTag]  WITH CHECK ADD  CONSTRAINT [FK_PostTag_Tags] FOREIGN KEY([TagID])
REFERENCES [dbo].[Tags] ([TagID])
GO
ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Tags]



USE [Pulog]
GO

/****** Object:  Table [dbo].[Attachment]    Script Date: 09/09/2009 23:11:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Attachment](
	[attachmentID] [int] IDENTITY(1,1) NOT NULL,
	[filePath] [nvarchar](500) NOT NULL,
	[originalName] [nvarchar](200) NOT NULL,
	[downloadCount] [int] NOT NULL,
	[uploadTime] [int] NOT NULL,
	[uploadUserID] [int] NOT NULL,
 CONSTRAINT [PK_Attachment] PRIMARY KEY CLUSTERED 
(
	[attachmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Attachment]  WITH NOCHECK ADD  CONSTRAINT [FK_Attachment_User] FOREIGN KEY([uploadUserID])
REFERENCES [dbo].[User] ([userID])
GO

ALTER TABLE [dbo].[Attachment] CHECK CONSTRAINT [FK_Attachment_User]
GO



USE [Pulog]
GO
/****** Object:  Table [dbo].[Links]    Script Date: 06/01/2009 17:02:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Links](
	[linkID] [int] IDENTITY(1,1) NOT NULL,
	[linkName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[linkDescription] [ntext] COLLATE Chinese_PRC_CI_AS NULL,
	[linkURL] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL,
	[linkOrder] [int] NULL,
 CONSTRAINT [PK_Links] PRIMARY KEY CLUSTERED 
(
	[linkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


USE [Pulog]
GO
/****** Object:  Table [dbo].[Settings]    Script Date: 06/01/2009 17:02:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Settings](
	[Key] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Value] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED 
(
	[Key] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Use [Pulog]

INSERT INTO [Pulog].[dbo].[Settings]
VALUES
	('BlogDescription', 'Blog Description', 'Shawn''s Blog...'),
	('BlogName', 'Blog Name', 'NZ Living...'),
	('BlogURL', 'Blog URL', 'http://localhost'),
	('MaxCommentLevel', 'Max Comment Reply Level', '5')
GO




/* change Post.pubTime into type int */
/* transfer data */
/* run following 
	update Post
	set tt = dateadd(ss,pubTime,'01/01/1970') */

/* change Post.pubTime into type Nvarchar 
	ALTER TABLE dbo.Post ALTER COLUMN pubTime Nvarchar(50)
*/
/* run following */
/*
	update Post
	set pubtime = '2009-1-1'
*/

/* change Post.pubTime into type DateTime 
	ALTER TABLE dbo.Post ALTER COLUMN pubTime datetime
*/
/* run following */
/*update Post
set pubTime = tt*/

/* delete column tt */

/* run following */
/*
update dbo.Post
set PostID = PostID + 1*/


/* run following 
		update Comment
		set tt = dateadd(ss,CommentTime,'01/01/1970')
   change Comment.CommentTime to type nvarchar
		ALTER TABLE dbo.Comment ALTER COLUMN CommentTime nvarchar(50)
   run following
		update Comment
		set CommentTime = '2009-1-1'
   change Post.pubTime into type DateTime 
		ALTER TABLE dbo.Comment ALTER COLUMN CommentTime datetime
   run following
		update Comment
		set CommentTime = tt
*/
	





/* convert ID column back to identity */
